﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using OgilvyOne.IDAL;
using OgilvyOne.Model;


namespace OgilvyOne.SqlServerDAL
{
    public class Login : ILogin
    {
        private const string SQL_SELECT_USER_NAMEPWD = "select aid,username,userrole from cms_users where username=@username and userpass=@userpass";
        
        public UsersModel login(string username, string password)
        {
            UsersModel user = null;
            SqlParameter[] parm = new SqlParameter[]{
                new SqlParameter("@username", SqlDbType.VarChar, 38,"username"),
                new SqlParameter("@userpass", SqlDbType.VarChar, 128,"userpass")
            };
            parm[0].Value = username;
            parm[1].Value = password;

            //Execute a query to read the products
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_USER_NAMEPWD, parm))
            {
                while (rdr.Read())
                {
                    user = new UsersModel();
                    user.Aid = rdr.GetInt32(0);
                    user.Username = username;
                    user.Userrole = rdr.GetString(2);
                }
            }

            return user;
        }



        private const string COLUMN_LIST_SQL = "select c.classid,c.classname,c.layerid,ct.backlink,parentid,isauditing,COLUMNTYPE from cms_column c left join cms_column_type ct on c.columntype=ct.columntypeid  where c.classid<> 1 order by parentid, c.orderid";
        public IList getColumnList()
        {
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, COLUMN_LIST_SQL, parms))
            {
                while (rdr.Read())
                {
                    UserColumnModel uc = new UserColumnModel();
                    uc.Columnid = rdr.GetInt32(0);
                    if(!rdr.IsDBNull(1))
                        uc.Columnname = rdr.GetString(1);
                    if (!rdr.IsDBNull(2))
                        uc.Layerid = rdr.GetString(2);
                    if (!rdr.IsDBNull(3))
                        uc.Columnurl = rdr.GetString(3);
                    if (!rdr.IsDBNull(4))
                        uc.Parentid = rdr.GetInt32(4);
                    if (!rdr.IsDBNull(5))
                        uc.Audit = rdr.GetInt32(5).ToString();
                    uc.Columntypeid = rdr.GetInt32(6);
                    datalist.Add(uc);
                }
            }

            return datalist;
        }

        private const string SYS_COLUMN_LIST_SQL = "select id,classname,layerid,linkurl,parentid from cms_syscolumn order by parentid, orderid";
        public IList getSysColumnList()
        {
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SYS_COLUMN_LIST_SQL, parms))
            {
                while (rdr.Read())
                {
                    UserColumnModel uc = new UserColumnModel();
                    uc.Columnid = rdr.GetInt32(0);
                    uc.Columnname = rdr.GetString(1);
                    uc.Layerid = rdr.GetString(2);
                    uc.Columnurl = rdr.GetString(3);
                    uc.Parentid = rdr.GetInt32(4);
                    datalist.Add(uc);
                }
            }

            return datalist;
        }
    }

    
}
